import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.util.Iterator;
import java.util.List;

import javax.imageio.ImageIO;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;

public class POIUtils {

	/**
	 * 复制行
	 * 
	 * @param wb Workbook
	 * @param sheet Sheet
	 * @param starRow 起始行（被复制的行）
	 * @param rows 复制多少行
	 */
	public static void insertRow(Workbook wb, Sheet sheet, int starRow, int rows) {
		// 更新名称区域
		POIUtils.updateName(wb, sheet, starRow,  rows);
		sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows, true, false);
		starRow = starRow - 1;

		for (int i = 0; i < rows; i++) {

			Row sourceRow = null;
			Row targetRow = null;
			Cell sourceCell = null;
			Cell targetCell = null;
			short m;

			starRow = starRow + 1;
			sourceRow = sheet.getRow(starRow);
			targetRow = sheet.createRow(starRow + 1);
			targetRow.setHeight(sourceRow.getHeight());

			for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
				sourceCell = sourceRow.getCell(m);
				targetCell = targetRow.createCell(m);

				targetCell.setCellStyle(sourceCell.getCellStyle());
				targetCell.setCellType(sourceCell.getCellType());
			}
		}
	}
	
	/**
	 * 更新名称区域
	 * 
	 * @param wb Workbook
	 * @param sheet Sheet
	 */
	public static void updateName(Workbook wb, Sheet sheet, int startRow, int offsetRow) {
		
		List names = wb.getAllNames();
		for(Object nameItem : names) {
			Name name = (Name)nameItem;
			
			if(!StringUtils.equals(name.getSheetName(), sheet.getSheetName())) {
				continue;
			}
			
			AreaReference cellReference = new AreaReference(name.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
			int firstCellRow = cellReference.getFirstCell().getRow();
			int firstCellCol = cellReference.getFirstCell().getCol();

			if(firstCellRow > startRow ) {
				
				int lastCellRow = cellReference.getLastCell().getRow();
				int lastCellCol = cellReference.getLastCell().getCol();
				AreaReference cellReferenceNew = wb.getCreationHelper().createAreaReference(new CellReference(firstCellRow + offsetRow,firstCellCol ), new CellReference(lastCellRow + offsetRow,lastCellCol ));
				name.setRefersToFormula(cellReference.getFirstCell().getSheetName() + "!" + cellReferenceNew.formatAsString());
			}
		}
	}
	
	/**
	 * 通过别名更新内容
	 * 
	 * @param wb Workbook
	 * @param alias 别名
	 * @param value 值
	 * @param offsetRow 偏移行
	 */
	public static void setCellValueByAlias(Workbook wb, String alias, String value, int offsetRow) {

		Name name = wb.getName(alias);  // This is core code

		if (name != null) {
			CellReference cellReference = new CellReference(name.getRefersToFormula());
			Sheet sheet = wb.getSheet(cellReference.getSheetName());
			Row row = sheet.getRow(cellReference.getRow() + offsetRow);
			Cell cell = row.getCell(cellReference.getCol());
			cell.setCellValue(value);
		}
	}

	public static void setCellValueByAlias(Workbook wb,Sheet sheet , String alias, String value, int offsetRow) {

		Name name = wb.getName(alias); // This is core code
		if (name != null) {
			CellReference cellReference = new CellReference(name.getRefersToFormula());
			Row row = sheet.getRow(cellReference.getRow() + offsetRow);
			Cell cell = row.getCell(cellReference.getCol());
			cell.setCellValue(value);
		}
	}

	/**
	 * 通过别名更新内容
	 * 
	 * @param wb Workbook
	 * @param alias 别名
	 * @param value 值
	 */
	public static void setCellValueByAlias(Workbook wb, String alias, String value) {
		POIUtils.setCellValueByAlias(wb, alias, value, 0);
	}

	/**
	 * 通过别名更新内容
	 * 
	 * @param wb Workbook
	 * @param pictureFileName 图片路径
	 * @param alias 别名
	 */
	public static void insertPicture(Workbook wb, String pictureFileName, String alias) {
		// 添加图片
		try {
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			BufferedImage bufferImg = ImageIO.read(new File(pictureFileName));
			ImageIO.write(bufferImg, FilenameUtils.getExtension(pictureFileName), byteArrayOut);

			Name name = wb.getName(alias);
			if (name != null) {
				AreaReference cellReference = new AreaReference(name.getRefersToFormula(), SpreadsheetVersion.EXCEL97);
				
				Sheet sheet = wb.getSheet(cellReference.getFirstCell().getSheetName());
				Drawing patriarch = sheet.createDrawingPatriarch();
				HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255,(short)(cellReference.getFirstCell().getCol()),  cellReference.getFirstCell().getRow() , (short)(cellReference.getLastCell().getCol()), cellReference.getLastCell().getRow());
				anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
				patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), getPictureType(pictureFileName)));
			}
		} catch (Exception e) {
		}
	}
	
	private static int getPictureType(String fileName) {
		String extName = FilenameUtils.getExtension(fileName);
		if (StringUtils.equalsAnyIgnoreCase(extName, "jpg", "jpeg")) {
			return HSSFWorkbook.PICTURE_TYPE_JPEG;
		}

		if (StringUtils.equalsAnyIgnoreCase(extName, "png")) {
			return HSSFWorkbook.PICTURE_TYPE_PNG;
		}

		return HSSFWorkbook.PICTURE_TYPE_JPEG;
	}
}